package com.hfi.service;

import com.hfi.model.*;
import com.hfi.repository.EmployeeLogRepository;
import com.hfi.repository.EmployeeRepository;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import javax.persistence.criteria.Predicate;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

/**
 * @author ChangLiang
 * @date 2019/6/3
 */
@Service
public class EmployeeService {

    @Autowired
    private EmployeeRepository employeeRepository;

    @Autowired
    private EmployeeLogRepository employeeLogRepository;

    @Autowired
    private EntityManager entityManager;

//    @Transactional
    public Employee addEmp(Employee employee) {
        Employee save = employeeRepository.save(employee);
        return save;
    }

    public Employee getEmployeeById(Long id) {
        Employee one = employeeRepository.findOne(id);
        return one;
    }

    public EmployeeNameOnly findById(Long id) {
        EmployeeNameOnly byId = employeeRepository.findById(id);
        return byId;
    }

    @Transactional(readOnly = true, isolation = Isolation.READ_UNCOMMITTED)
    public Page<Employee> selectPageEmployee(Employee employee){

        Page<Employee> employees = employeeRepository.findAll(getEmployeeWhereClause(employee),
                new PageRequest(employee.getPageNum() - 1,
                employee.getPageSize(), new Sort(Sort.Direction.DESC, "id")));
        System.out.println("employee: "+employees.getContent().size());
        try {
            Thread.sleep(10000);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        Page<Employee> employees2 = employeeRepository.findAll(getEmployeeWhereClause(employee),
                new PageRequest(employee.getPageNum() - 1,
                        employee.getPageSize(), new Sort(Sort.Direction.DESC, "id")));
        System.out.println("employee2: "+employees2.getContent().size());
        return employees2;
    }

    public List<EmployeeNameOnlyDto> selectPageEmployeeNameOnlyDto(Employee employee){
        return employeeRepository.findLastNamePage((employee.getPageNum()-1)*employee.getPageSize(),
                employee.getPageSize());
    }

    public List<Employee> selectPageEmployeeEntityManager(Employee employee){
        String sql = "select last_name from tbl_employee order by id desc limit ?,?";
        Query query = entityManager.createNativeQuery(sql);
        query.setParameter(1, (employee.getPageNum() - 1) * employee.getPageSize());
        query.setParameter(2, employee.getPageSize());
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List resultList = query.getResultList();
        for (Object o : resultList) {
            HashMap map = (HashMap) o;
            String lastName = (String) map.get("last_name");
            System.out.println(lastName);
        }
        return resultList;
    }

    public List<EmployeeNameOnlyDto> selectPageEmployeeEntityManager2(Employee employee){
        // 这段代码写在这里并不好 所有查询数据库的内容应该都放到EmployeeRepository中
        // 所以应该把这段代码放到EmployeeRepositoryImpl中
        String sql = "select last_name as lastName from tbl_employee order by id desc limit ?,?";
        Query query = entityManager.createNativeQuery(sql);
        query.setParameter(1, (employee.getPageNum() - 1) * employee.getPageSize());
        query.setParameter(2, employee.getPageSize());
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.aliasToBean(EmployeeNameOnlyDto.class));
        List resultList = query.getResultList();
        return resultList;
    }

    public List<EmployeeAndLog> selectEmployeeAndLog(Long id){
        // 这段代码写在这里并不好 所有查询数据库的内容应该都放到EmployeeRepository中
        // 所以应该把这段代码放到EmployeeRepositoryImpl中
        String sql = "select t1.id as id, t1.age, t2.log from tbl_employee t1 join tbl_employee_log t2 on t1" +
                ".id = t2.id " +
                "where t1.id = ?";
        Query query = entityManager.createNativeQuery(sql);
        query.setParameter(1, id);
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.aliasToBean(EmployeeAndLog.class));
        List resultList = query.getResultList();
        return resultList;
    }

    public Long getEmployeeCount() {
        return employeeRepository.count();
    }

    private Specification<Employee> getEmployeeWhereClause(Employee employee) {
        return (root, query, cb) -> {
            List<Predicate> predicates = new ArrayList<>();
            if (employee.getId() != null) {
                predicates.add(cb.equal(root.get("id").as(Long.class), employee.getId()));
            }
            if (employee.getGender() != null) {
                predicates.add(cb.equal(root.get("gender").as(String.class), employee.getGender()));
            }
            if (employee.getAge() != null) {
                predicates.add(cb.equal(root.get("age").as(Integer.class), employee.getAge()));
            }
            if (employee.getLastName() != null) {
                predicates.add(cb.like(root.get("lastName").as(String.class), "%" + employee.getLastName() + "%"));
            }
            if (employee.getEmail() != null) {
                predicates.add(cb.like(root.get("email").as(String.class), employee.getEmail()));
            }
            Predicate[] pres = new Predicate[predicates.size()];
            query.where(predicates.toArray(pres));
            return query.getRestriction();
        };
    }

    @Transactional(rollbackFor = Exception.class)
    public Integer updateEmailById(Employee employee) {
        Integer integer = employeeRepository.updateEmployeeEmail(employee.getId(), employee.getEmail());
        return integer;
    }

    public Employee getEmployeeById2(Long id) {
        return employeeRepository.getEmployeeById2(id);
    }

    @Transactional(isolation = Isolation.READ_UNCOMMITTED)
    public Employee saveTwoEmployee(Employee employee) {
        // 模拟两次执行
        Employee employee2 = addEmp(employee);

        try {
            Thread.sleep(5000);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }

//        int template = 5/0;
        EmployeeLog employeeLog = new EmployeeLog();
        employeeLog.setUserId(employee2.getId());
        employeeLog.setLog("add emp");
        employeeLogRepository.save(employeeLog);
        return employee2;

        /*Employee employee1 = new Employee();
        employee1.setLastName("cl");
        employee1.setId(1L);
        employee1.setEmail(employee2.getEmail());
        employee1 = addEmp(employee1);
        return employee1;*/
    }

    public EmployeeMixDto getEmployeeMixDto(String mixId) {
        return employeeRepository.getEmployeeMixDto(mixId);
    }

    public List<EmployeeMixDto> getPageEmployeeMixDto(Employee employee) {
        return employeeRepository.getPageEmployeeMixDto(employee);
    }
}
